/*
	EXEC sp_booking_report_proc '0','0','0','0','0','0','0','0','0','booking',1,'0','byweek','0'
	EXEC sp_booking_report_proc '0','0','0','0','0','0','sriendcust','0','0','0',1,'0','0','XLREPORT'
	EXEC sp_booking_report_proc '7/3/2008','0','0','0','0','0','0','0','0','CHANGEDATE',1,'0','0','0'	

	SO_ENDCUSTOMER_ID ---> end user
	SO.SO_ODM_ID	  ----> END CUSTOMER
*/
alter PROC sp_booking_report_proc

@FROMDATE	VARCHAR(50),
@TODATE		VARCHAR(50),
@CUSTOMERID	VARCHAR(200),
@PRODUCTID	VARCHAR(200),
@REGION		VARCHAR(50),
@REPOFFICE	VARCHAR(50),
@ENDCUSTOMER	VARCHAR(500),--parameter for end customer
@SALESPERSON	VARCHAR(500),
@DEFAULTPROMPT	VARCHAR(50),
@BOOKINGTYPE	VARCHAR(50),
@ORDERTYPE	INT,
@ENDUSER	VARCHAR(500),--parameter for end user
@BYPERIOD	VARCHAR(50),
@REPORTTYPE	VARCHAR(50)

AS
BEGIN

	CREATE TABLE #BOOKINGTABLE 
		( SALESORDERID INT,[LINEITEM ID] INT, [CHANGE NO] INT,[LINE ITEM NO] INT,PRODUCTID INT,QUANTITY INT,
		  [UNIT PRICE] FLOAT,AMOUNT FLOAT,[REQUESTED DOCK DATE] DATETIME, [REQUESTED DATE WW NO] INT, [REQUESTED DATE QTR NO] INT,
		  [SCHEDULE DATE] DATETIME,[SCHEDULE DATE WW NO] INT,[SCHEDULE DATE QTR NO] INT,
		  [REVISED DATE] DATETIME,[REVISED DATE WW NO] INT,[REVISED DATE QTR NO] INT,
		  [BOOKING DATE] DATETIME,[BOOKING DATE WW NO] INT,[BOOKING DATE QTR NO] INT,
		  [CHANGE DATE] DATETIME,[CHANGE DATE WW NO] INT,[CHANGE DATE QTR NO] INT,
		  [SALES ORDER NO] VARCHAR(50),CUSTOMERID INT,[SALES PERSON] VARCHAR(50),[END CUSTOMER] VARCHAR(50),
		  [REGION REP] VARCHAR(50),[END USER] VARCHAR(50),CUSTOMER VARCHAR(50), [PRODUCT] VARCHAR(50), BOOKINGTYPE BIT, ISCONSIGNMENT BIT, CREDIT_NOTE_REASON VARCHAR(100) )
				
	CREATE TABLE #INITIAL_RESULTS
		(SALESORDER_ID INT, LINEITEM_ID INT, CHANGENO INT, QTY INT, UNITCOST FLOAT )

	DECLARE @CHANGE_LINEITEMID INT,@CHANGE_SALESORDERID INT,@CHANGENO INT,@CHANGE_QTY INT,@CHANGE_UNIT_COST FLOAT,@CHANGE_AMOUNT FLOAT
	DECLARE @QUANTITY INT,@UNITCOST FLOAT,@AMOUNT FLOAT
	DECLARE @NEG_POS_BK_I BIT
	DECLARE @INITIAL_UNIT_COST FLOAT, @INSERT_UNIT_COST FLOAT, @INSERT_AMOUNT FLOAT
	DECLARE @INITIAL_QTY INT, @INSERT_QTY INT, @INSERTCHANGE_NO INT
	DECLARE @PRODUCT_ID INT,@CCO_REQUESTED_DATE DATETIME,@CCO_BOOKING_DATE DATETIME,@CCO_SCHEDULE_DATE DATETIME,@CCO_REVISED_DATE DATETIME,
		@CCO_BOOKING_DATE_WW_NO INT,@CCO_BOOKING_DATE_QTR_NO INT,@CCO_REQUESTED_DATE_WW_NO INT,@CCO_REQUESTED_DATE_QTR_NO INT,
		@CCO_SCHEDULE_DATE_WW_NO INT,@CCO_SCHEDULE_DATE_QTR_NO INT,@CCO_REVISED_DATE_WW_NO INT,@CCO_REVISED_DATE_QTR_NO INT
	DECLARE @LINE_ITEM_NO VARCHAR(50)
	DECLARE @SALES_ORDER_NO VARCHAR(50),@CUSTOMER_ID INT,@SALES_PERSON VARCHAR(50),@END_CUSTOMER VARCHAR(50),@REGION_REP VARCHAR(50),@END_USER VARCHAR(50),@CUSTOMER VARCHAR(50),@PRODUCT VARCHAR(50)
	DECLARE @ACTUAL_YEAR INT,@PERIOD_VALUE INT,@BOOKINGDATE DATETIME,@BOOKING_DATE_WW_NO INT,@BOOKING_DATE_QTR_NO INT
	DECLARE @ISCONSIGNMENT BIT

	DECLARE @BOOKINGSTRING 		VARCHAR(4000)
	DECLARE @BOOKINGFROMSTRING	VARCHAR(4000)
	DECLARE @CANCELSTRING 		VARCHAR(4000)
	DECLARE @CANCELFROMSTRING 	VARCHAR(4000)
	DECLARE @CREDIT_NOTES_STRING	VARCHAR(4000)
	DECLARE @CREDIT_NOTES_FROM_STRING VARCHAR(4000)
	DECLARE @RESULT_STR NVARCHAR(4000)

	SET @BOOKINGSTRING = N'
	insert into #BOOKINGTABLE
	SELECT
		 ccol.Salesorder_ID, LineItem_ID, ChangeNo, CCO_Line_Item_No, ccol.Product_ID,CCO_Qty, CCO_Unit_Cost,(CCO_Qty*CCO_Unit_Cost),
		 CCO_Requested_Date,CCO_REQUESTED_DATE_WW_NO, CCO_REQUESTED_DATE_QTR_NO, 
		 CCO_Schedule_Date, CCO_SCHEDULE_DATE_WW_NO,CCO_SCHEDULE_DATE_QTR_NO,
		 CCO_Revised_Date,CCO_REVISED_DATE_WW_NO,CCO_REVISED_DATE_QTR_NO,
		 SO_Booking_Date,SO_BOOKING_DATE_WW_NO,SO_BOOKING_DATE_QTR_NO,
		 CCOL.TIMESTAMP,CCOL.TIMESTAMP_WW_NO,CCOL.TIMESTAMP_QTR_NO,--cco_booking_date_ww_no,cco_booking_date_QTR_no,
		 SO.SalesOrder_NO,SO.Customer_ID,SO.SO_Sales_Person, 
		 CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) 
			THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
	 	 END,
		SO.Regional_Rep,
		CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) 
		THEN (CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) 
			THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
			END) 
		ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
		END, C_NAME, PART_NUMBER ,0,SO_IS_CONSIGNMENT, NULL '

	SET @BOOKINGFROMSTRING = N'		
	FROM customer_change_order_lineitem CCOL, SALES_ORDER SO, customer c, product p
	WHERE CCOL.SALESORDER_ID = SO.SALESORDER_ID and so.customer_id = c.customer_id and ccol.product_id = p.product_id 
	and changeNo = 0 '

	IF @PRODUCTID IS NOT NULL AND @PRODUCTID <> '0'
	BEGIN
		SET @BOOKINGFROMSTRING = @BOOKINGFROMSTRING + ' AND ccol.Product_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PRODUCTID +'''))'
	END
	IF @CUSTOMERID IS NOT NULL AND @CUSTOMERID <> '0'
	BEGIN
		SET @BOOKINGFROMSTRING = @BOOKINGFROMSTRING + ' AND SO.Customer_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @CUSTOMERID +'''))'
	END
	IF @REGION IS NOT NULL AND @REGION <> '0'
	BEGIN
		SET @BOOKINGFROMSTRING = @BOOKINGFROMSTRING + ' AND ISNULL( ISNULL( (SELECT C_Region FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID AND LEN(RTRIM(LTRIM(C_Region)) ) > 0 ),(SELECT C_Region FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID AND LEN(RTRIM(LTRIM(C_Region)) ) > 0 ) ), C.C_Region ) = '''+ @REGION+''''
	END
	IF @REPOFFICE IS NOT NULL AND @REPOFFICE <> '0'
	BEGIN
		SET @BOOKINGFROMSTRING = @BOOKINGFROMSTRING + ' AND SO.REGIONAL_REP = '''+ @REPOFFICE+''''
	END
	IF @ENDCUSTOMER IS NOT NULL AND @ENDCUSTOMER <> '0'
	BEGIN
		SET @BOOKINGFROMSTRING = @BOOKINGFROMSTRING + ' AND CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) 
								THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
								ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
						 	 END IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @ENDCUSTOMER +'''))'
	END
	IF @SALESPERSON IS NOT NULL AND @SALESPERSON <> '0'
	BEGIN
		SET @BOOKINGFROMSTRING = @BOOKINGFROMSTRING + ' AND C.C_SALES_PERSON IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @SALESPERSON +'''))'
	END
	IF @ENDUSER IS NOT NULL AND @ENDUSER <> '0'
	BEGIN
		SET @BOOKINGFROMSTRING = @BOOKINGFROMSTRING + ' AND CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) 
								THEN (CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) 
									THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
									ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
									END) 
								ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
								END IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @ENDUSER +'''))'
	END

	IF @ORDERTYPE IS NOT NULL AND @ORDERTYPE <> 2
	BEGIN
		SET @BOOKINGFROMSTRING = @BOOKINGFROMSTRING + ' AND SO_IS_CONSIGNMENT = 1 AND SO.SO_Is_Sample = '''+ CONVERT(VARCHAR,@ORDERTYPE)+''''
	END
	ELSE IF @ORDERTYPE IS NOT NULL AND @ORDERTYPE = 2
	BEGIN
		SET @BOOKINGFROMSTRING = @BOOKINGFROMSTRING + ' AND SO_IS_CONSIGNMENT = 0 '
	END

	IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' )
	BEGIN
		/*IF ( @BOOKINGTYPE = 'CHANGEDATE' )
		 BEGIN
			SET @BOOKINGFROMSTRING = @BOOKINGFROMSTRING +' AND CCOL.TIMESTAMP >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
		 END
		ELSE*/
		IF ( @BOOKINGTYPE <> 'CHANGEDATE' )
		 BEGIN
			SET @BOOKINGFROMSTRING = @BOOKINGFROMSTRING +' AND SO_Booking_Date >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
		 END
	END
	IF ( @TODATE IS NOT NULL AND @TODATE <> '0' )
	BEGIN
		/*IF ( @BOOKINGTYPE = 'CHANGEDATE' )
		 BEGIN
			SET @BOOKINGFROMSTRING = @BOOKINGFROMSTRING +' AND CCOL.TIMESTAMP <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
		 END
		ELSE*/
		IF ( @BOOKINGTYPE <> 'CHANGEDATE' )
		 BEGIN
			SET @BOOKINGFROMSTRING = @BOOKINGFROMSTRING + +' AND SO_Booking_Date <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
		 END
	END
	
	--PRINT (@BOOKINGSTRING)
	EXEC (@BOOKINGSTRING+@BOOKINGFROMSTRING)

	IF ( @BOOKINGTYPE = 'CHANGEDATE' AND ( (@FROMDATE IS NOT NULL AND @FROMDATE <> '0') OR (@TODATE IS NOT NULL AND @TODATE <> '0') ) )
	BEGIN
		--NEED TO DELETE LINEITEMS WHICH DOES NOT FALL IN CHANGE DATE
		DECLARE @DELETE_STR NVARCHAR(500)
		SET @DELETE_STR = 'DELETE FROM #BOOKINGTABLE WHERE [LINEITEM ID] NOT IN ( SELECT DISTINCT LINEITEM_ID FROM CUSTOMER_CHANGE_ORDER_LINEITEM WHERE 1=1 '
		IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' )
		BEGIN
			SET @DELETE_STR = @DELETE_STR + ' AND TIMESTAMP >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
		END
		IF ( @TODATE IS NOT NULL AND @TODATE <> '0' )
		BEGIN
			SET @DELETE_STR = @DELETE_STR + ' AND TIMESTAMP <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
		END
		SET @DELETE_STR = @DELETE_STR + ' )'

		EXEC (@DELETE_STR)
	END

	INSERT INTO #INITIAL_RESULTS (SalesOrder_ID, LineItem_ID, ChangeNo, Qty, UnitCost ) 
		select SALESORDERID,[LINEITEM ID],[CHANGE NO],QUANTITY,[UNIT PRICE] from #BOOKINGTABLE
	
	DECLARE CHANGECURSOR CURSOR FOR 
		SELECT 
			LINEITEM_ID,SALESORDER_ID,CHANGENO,CCO_QTY,CCO_UNIT_COST--,CCO_AMOUNT 
		FROM 
			CUSTOMER_CHANGE_ORDER_LINEITEM 
		WHERE 
			CHANGENO <> 0 
			AND LINEITEM_ID IN ( SELECT [LINEITEM ID] FROM #BOOKINGTABLE)
		ORDER BY CHANGENO

	OPEN CHANGECURSOR
	FETCH NEXT FROM CHANGECURSOR INTO @CHANGE_LINEITEMID,@CHANGE_SALESORDERID,@CHANGENO,@CHANGE_QTY,@CHANGE_UNIT_COST--,@CHANGE_AMOUNT
	WHILE @@FETCH_STATUS = 0
	BEGIN
		select 
			@INSERTCHANGE_NO= max(ChangeNo) 
		from #INITIAL_RESULTS 
		where 
			SalesOrder_ID = @CHANGE_SALESORDERID 
			and LINEITEM_ID = @CHANGE_LINEITEMID
		--(SalesOrder_ID int, LineItem_ID int, ChangeNo int, Qty int, UnitCost float )
		select 
			@initial_Qty = Qty,@initial_Unit_Cost = UnitCost
		FROM 
			#INITIAL_RESULTS 
		where 
			SalesOrder_ID = @CHANGE_SALESORDERID 
			and LINEITEM_ID = @CHANGE_LINEITEMID
			AND ChangeNo = @INSERTCHANGE_NO

		SELECT 
			@SALES_ORDER_NO = [SALES ORDER NO],@CUSTOMER_ID = CUSTOMERID,@SALES_PERSON = [SALES PERSON],@END_CUSTOMER = [END CUSTOMER],
					  @REGION_REP = [REGION REP],@END_USER = [END USER],@CUSTOMER = CUSTOMER, @PRODUCT = [PRODUCT],
					  @BOOKINGDATE = [BOOKING DATE],@BOOKING_DATE_WW_NO = [BOOKING DATE WW NO],@BOOKING_DATE_QTR_NO = [BOOKING DATE QTR NO],
					  @ISCONSIGNMENT = ISCONSIGNMENT
		FROM 
			#BOOKINGTABLE 
		WHERE 
			[LINEITEM ID] = @CHANGE_LINEITEMID 
			AND SALESORDERID = @CHANGE_SALESORDERID 
			AND [CHANGE NO] = @INSERTCHANGE_NO

		SELECT 
			@product_id = product_id,@LINE_ITEM_NO = CCO_LINE_ITEM_NO,@cco_requested_date = cco_requested_date,
			@cco_booking_date = [TIMESTAMP],@cco_schedule_date = cco_schedule_date,@cco_revised_date = cco_revised_date,
			@cco_booking_date_ww_no = TIMESTAMP_WW_NO,@cco_booking_date_qtr_no = TIMESTAMP_QTR_NO,
			@cco_requested_date_ww_no = cco_requested_date_ww_no,@cco_requested_date_qtr_no = cco_requested_date_qtr_no,
			@cco_schedule_date_ww_no = cco_schedule_date_ww_no,@cco_schedule_date_qtr_no = cco_schedule_date_qtr_no,
			@cco_revised_date_ww_no = cco_revised_date_ww_no,@cco_revised_date_qtr_no = cco_revised_date_qtr_no
		FROM 
			CUSTOMER_CHANGE_ORDER_LINEITEM 
		where 
			LINEITEM_ID = @CHANGE_LINEITEMID AND SALESORDER_ID = @CHANGE_SALESORDERID AND CHANGENO = @CHANGENO

		IF @BOOKINGTYPE <> 'CHANGEDATE'
		BEGIN
			SET @ACTUAL_YEAR  = DATEPART(yyyy,@BOOKINGDATE)

			IF @BYPERIOD = 'BYWEEK'
			BEGIN
				SET @PERIOD_VALUE = @BOOKING_DATE_WW_NO
			END
			ELSE IF @BYPERIOD = 'BYMONTH'
			BEGIN
				SET @PERIOD_VALUE = DATEPART(m,@BOOKINGDATE)
			END
			ELSE
			BEGIN
				SET @PERIOD_VALUE = @BOOKING_DATE_QTR_NO
			END	
		END
		ELSE
		BEGIN
			SET @ACTUAL_YEAR  = DATEPART(yyyy,@cco_booking_date)

			IF @BYPERIOD = 'BYWEEK'
			BEGIN
				SET @PERIOD_VALUE = @cco_booking_date_ww_no
			END
			ELSE IF @BYPERIOD = 'BYMONTH'
			BEGIN
				SET @PERIOD_VALUE = DATEPART(m,@cco_booking_date)
			END
			ELSE
			BEGIN
				SET @PERIOD_VALUE = @cco_booking_date_qtr_no
			END
		END
		--look for the changes in amt or qty or price
		set @insert_Unit_Cost = @CHANGE_UNIT_COST - @initial_Unit_Cost

		set @insert_Qty = @CHANGE_QTY - @initial_Qty
											
		--If there are no changes then set them back to default
		if @insert_Unit_Cost = 0
			set @insert_Unit_Cost = @CHANGE_UNIT_COST
		if @insert_Qty = 0
			set @insert_Qty = @CHANGE_QTY

		--set @insert_Amount = @insert_Unit_Cost * @insert_Qty

		insert into #INITIAL_RESULTS values (@CHANGE_SALESORDERID, @CHANGE_LINEITEMID, @CHANGENO, @CHANGE_QTY,@CHANGE_UNIT_COST)
		
		if( @CHANGE_QTY <> @initial_Qty )
		begin
			if ( @initial_Qty < 0 )
			set @neg_Pos_Bk_I = 1
						
			INSERT INTO #BOOKINGTABLE 
					( SALESORDERID,[LINEITEM ID],[CHANGE NO],[LINE ITEM NO],PRODUCTID,QUANTITY,
					  [UNIT PRICE],[AMOUNT],[REQUESTED DOCK DATE],[REQUESTED DATE WW NO],[REQUESTED DATE QTR NO],
					  [SCHEDULE DATE],[SCHEDULE DATE WW NO],[SCHEDULE DATE QTR NO],
					  [REVISED DATE],[REVISED DATE WW NO],[REVISED DATE QTR NO],
					  [BOOKING DATE],[BOOKING DATE WW NO],[BOOKING DATE QTR NO],
					  [CHANGE DATE],[CHANGE DATE WW NO],[CHANGE DATE QTR NO],
					  [SALES ORDER NO],CUSTOMERID,[SALES PERSON],[END CUSTOMER],
					  [REGION REP],[END USER],CUSTOMER, [PRODUCT],BOOKINGTYPE,ISCONSIGNMENT)

				VALUES 	( @CHANGE_SALESORDERID,@CHANGE_LINEITEMID,@CHANGENO,@LINE_ITEM_NO, @product_id,@insert_Qty,
					  @CHANGE_UNIT_COST,(@insert_Qty*@CHANGE_UNIT_COST),@cco_requested_date,@cco_requested_date_ww_no,@cco_requested_date_qtr_no,
					  @cco_schedule_date,@cco_schedule_date_ww_no,@cco_schedule_date_qtr_no,
					  @cco_revised_date,@cco_revised_date_ww_no,@cco_revised_date_qtr_no,
					  @BOOKINGDATE,@BOOKING_DATE_WW_NO,@BOOKING_DATE_QTR_NO,
					  @cco_booking_date,@cco_booking_date_ww_no,@cco_booking_date_qtr_no,
					  @SALES_ORDER_NO,@CUSTOMER_ID,@SALES_PERSON,@END_CUSTOMER,
					  @REGION_REP,@END_USER,@CUSTOMER, @PRODUCT,1,@ISCONSIGNMENT)
		end

		set @neg_Pos_Bk_I = 0

		if(@CHANGE_UNIT_COST <> @initial_Unit_Cost)
		begin
			if ( @insert_Unit_Cost < 0 )
			set @neg_Pos_Bk_I = 1
			
			INSERT INTO #BOOKINGTABLE 
					( SALESORDERID,[LINEITEM ID],[CHANGE NO],[LINE ITEM NO],PRODUCTID,QUANTITY,
					  [UNIT PRICE],AMOUNT,[REQUESTED DOCK DATE],[REQUESTED DATE WW NO],[REQUESTED DATE QTR NO],
					  [SCHEDULE DATE],[SCHEDULE DATE WW NO],[SCHEDULE DATE QTR NO],
					  [REVISED DATE],[REVISED DATE WW NO],[REVISED DATE QTR NO],
					  [BOOKING DATE],[BOOKING DATE WW NO],[BOOKING DATE QTR NO],
					  [CHANGE DATE],[CHANGE DATE WW NO],[CHANGE DATE QTR NO],
					  [SALES ORDER NO],CUSTOMERID,[SALES PERSON],[END CUSTOMER],
					  [REGION REP],[END USER],CUSTOMER, [PRODUCT],BOOKINGTYPE,ISCONSIGNMENT)

				VALUES 	( @CHANGE_SALESORDERID,@CHANGE_LINEITEMID,@CHANGENO,@LINE_ITEM_NO, @product_id,@initial_Qty,
					  @insert_Unit_Cost,(@initial_Qty*@insert_Unit_Cost),@cco_requested_date,@cco_requested_date_ww_no,@cco_requested_date_qtr_no,
					  @cco_schedule_date,@cco_schedule_date_ww_no,@cco_schedule_date_qtr_no,
					  @cco_revised_date,@cco_revised_date_ww_no,@cco_revised_date_qtr_no,
					  @BOOKINGDATE,@BOOKING_DATE_WW_NO,@BOOKING_DATE_QTR_NO,
					  @cco_booking_date,@cco_booking_date_ww_no,@cco_booking_date_qtr_no,
					  @SALES_ORDER_NO,@CUSTOMER_ID,@SALES_PERSON,@END_CUSTOMER,
					  @REGION_REP,@END_USER,@CUSTOMER, @PRODUCT,1,@ISCONSIGNMENT)
		end


		if (@CHANGE_QTY = @initial_Qty and @CHANGE_UNIT_COST = @initial_Unit_Cost ) 
		begin
			set @neg_Pos_Bk_I = 0
		
			INSERT INTO #BOOKINGTABLE 
					( SALESORDERID,[LINEITEM ID],[CHANGE NO],[LINE ITEM NO],PRODUCTID,QUANTITY,
					  [UNIT PRICE],AMOUNT,[REQUESTED DOCK DATE],[REQUESTED DATE WW NO],[REQUESTED DATE QTR NO],
					  [SCHEDULE DATE],[SCHEDULE DATE WW NO],[SCHEDULE DATE QTR NO],
					  [REVISED DATE],[REVISED DATE WW NO],[REVISED DATE QTR NO],
					  [BOOKING DATE],[BOOKING DATE WW NO],[BOOKING DATE QTR NO],
					  [CHANGE DATE],[CHANGE DATE WW NO],[CHANGE DATE QTR NO],
					  [SALES ORDER NO],CUSTOMERID,[SALES PERSON],[END CUSTOMER],
					  [REGION REP],[END USER],CUSTOMER, [PRODUCT],BOOKINGTYPE,ISCONSIGNMENT)
				VALUES 	( @CHANGE_SALESORDERID,@CHANGE_LINEITEMID,@CHANGENO,@LINE_ITEM_NO, @product_id,0,
					  0.00,0.00,@cco_requested_date,@cco_requested_date_ww_no,@cco_requested_date_qtr_no,
					  @cco_schedule_date,@cco_schedule_date_ww_no,@cco_schedule_date_qtr_no,
					  @cco_revised_date,@cco_revised_date_ww_no,@cco_revised_date_qtr_no,
					  @BOOKINGDATE,@BOOKING_DATE_WW_NO,@BOOKING_DATE_QTR_NO,
					  @cco_booking_date,@cco_booking_date_ww_no,@cco_booking_date_qtr_no,
					  @SALES_ORDER_NO,@CUSTOMER_ID,@SALES_PERSON,@END_CUSTOMER,
					  @REGION_REP,@END_USER,@CUSTOMER, @PRODUCT,0,@ISCONSIGNMENT)
		end
		
			SET @CHANGE_SALESORDERID = NULL
			SET @CHANGE_LINEITEMID = NULL
			SET @CHANGENO = NULL
			SET @LINE_ITEM_NO = NULL
			SET @product_id = NULL
			SET @insert_Qty = NULL
			SET @insert_Unit_Cost = NULL
			SET @cco_requested_date = NULL
			SET @cco_requested_date_ww_no = NULL
			SET @cco_requested_date_qtr_no = NULL
			SET @cco_schedule_date = NULL
			SET @cco_schedule_date_ww_no = NULL
			SET @cco_schedule_date_qtr_no = NULL
			SET @cco_revised_date = NULL
			SET @cco_revised_date_ww_no = NULL
			SET @cco_revised_date_qtr_no = NULL
			SET @cco_booking_date = NULL
			SET @cco_booking_date_ww_no = NULL
			SET @cco_booking_date_qtr_no = NULL
			SET @SALES_ORDER_NO = NULL
			SET @CUSTOMER_ID = NULL
			SET @SALES_PERSON = NULL
			SET @END_CUSTOMER = NULL
			SET @REGION_REP = NULL
			SET @END_USER = NULL
			SET @CUSTOMER = NULL
			SET @PRODUCT = NULL

			SET @neg_Pos_Bk_I = NULL
			SET @initial_Unit_Cost = NULL
			SET @insert_Unit_Cost = NULL
			SET @insert_Amount = NULL
			SET @initial_Qty = NULL
			SET @insert_Qty  = NULL
			SET @INSERTCHANGE_NO  = NULL
			SET @ACTUAL_YEAR = NULL
			SET @PERIOD_VALUE = NULL
			SET @ISCONSIGNMENT = NULL

	FETCH NEXT FROM CHANGECURSOR INTO @CHANGE_LINEITEMID,@CHANGE_SALESORDERID,@CHANGENO,@CHANGE_QTY,@CHANGE_UNIT_COST--,@CHANGE_AMOUNT
	END
	CLOSE CHANGECURSOR
	DEALLOCATE CHANGECURSOR

	set @CANCELSTRING = N' 
	insert into #BOOKINGTABLE (SALESORDERID,[LINEITEM ID],[LINE ITEM NO],PRODUCTID,QUANTITY,
		  [UNIT PRICE],AMOUNT,[REQUESTED DOCK DATE],[REQUESTED DATE WW NO],[REQUESTED DATE QTR NO],
		  [SCHEDULE DATE],[SCHEDULE DATE WW NO],[SCHEDULE DATE QTR NO],
		  [REVISED DATE],[REVISED DATE WW NO],[REVISED DATE QTR NO],
		  [BOOKING DATE],[BOOKING DATE WW NO],[BOOKING DATE QTR NO],
		  [CHANGE DATE],[CHANGE DATE WW NO],[CHANGE DATE QTR NO],
		  [SALES ORDER NO],CUSTOMERID,[SALES PERSON],[END CUSTOMER],
		  [REGION REP],[END USER],CUSTOMER, [PRODUCT], BOOKINGTYPE, ISCONSIGNMENT )
	select 
		CCOL.Salesorder_ID, LineItem_ID, CCOL_Line_Item_No, ccol.Product_ID,(CCOL_Qty*-1), (CCOL_Unit_Cost*-1),((CCOL_Qty*-1)*(CCOL_Unit_Cost)),  
		CCOL_Requested_Date,CCOL_REQUESTED_DATE_WW_NO,ccol_REQUESTED_DATE_QTR_NO, 
		CCOL_Schedule_Date,CCOL_SCHEDULE_DATE_WW_NO,ccol_SCHEDULE_DATE_QTR_NO,
		CCOL_Revised_Date ,CCOL_REVISED_DATE_WW_NO,CCOL_REVISED_DATE_QTR_NO,
		so_Booking_Date,SO_BOOKING_DATE_WW_NO,SO_BOOKING_DATE_QTR_NO,
		CCOL.Timestamp,CCOL.Timestamp_WW_NO,CCOL.Timestamp_QTR_NO,
		SO.SalesOrder_NO,SO.Customer_ID,SO.SO_Sales_Person,
		CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) 
			THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
		END,Regional_Rep,
		CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) 
			THEN (CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) 
				THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
				ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
				END) 
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
		END,
		C_NAME,PART_NUMBER,1,SO_IS_CONSIGNMENT '

	SET @CANCELFROMSTRING = N' 
	from  
		customer_cancel_order_lineitem CCOL,Sales_Order SO, customer c, product p
	where 
		CCOL.SalesOrder_Id = SO.SalesOrder_Id 
		and ccol.product_id = p.product_id
		and so.customer_id = c.customer_id '

	IF @PRODUCTID <> '0'
	BEGIN
		SET @CANCELFROMSTRING = @CANCELFROMSTRING + ' AND CCOL.Product_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PRODUCTID +'''))'
	END
	IF @CUSTOMERID <> '0'
	BEGIN
		SET @CANCELFROMSTRING = @CANCELFROMSTRING + ' AND SO.Customer_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @CUSTOMERID +'''))'
	END
	IF @REGION <> '0'
	BEGIN
		SET @CANCELFROMSTRING = @CANCELFROMSTRING + ' AND C.C_REGION = '''+ @REGION+''''
	END
	IF @REPOFFICE <> '0'
	BEGIN
		SET @CANCELFROMSTRING = @CANCELFROMSTRING + ' AND SO.REGIONAL_REP = '''+ @REPOFFICE+''''
	END
	IF @ENDCUSTOMER <> '0'
	BEGIN
		SET @CANCELFROMSTRING = @CANCELFROMSTRING + ' AND CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) 
								THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
								ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
							END IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @ENDCUSTOMER +'''))'
	END
	IF @SALESPERSON <> '0'
	BEGIN
		SET @CANCELFROMSTRING = @CANCELFROMSTRING + ' AND C.C_SALES_PERSON IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @SALESPERSON +'''))'
	END
	IF @ENDUSER <> '0'
	BEGIN
		SET @CANCELFROMSTRING = @CANCELFROMSTRING + ' AND CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) 
								THEN (CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) 
									THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
									ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
									END) 
								ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
							END IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @ENDUSER +'''))'
	END

	IF @ORDERTYPE IS NOT NULL AND @ORDERTYPE <> 2
	BEGIN
		SET @CANCELFROMSTRING = @CANCELFROMSTRING + ' AND SO_IS_CONSIGNMENT = 1 AND SO.SO_Is_Sample = '''+ CONVERT(VARCHAR,@ORDERTYPE)+''''
	END
	ELSE IF @ORDERTYPE IS NOT NULL AND @ORDERTYPE = 2
	BEGIN
		SET @CANCELFROMSTRING = @CANCELFROMSTRING + ' AND SO_IS_CONSIGNMENT = 0 '
	END
	
	IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' )
	BEGIN
		SET @CANCELFROMSTRING = @CANCELFROMSTRING +' AND CCOL.Timestamp >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
	END
	IF ( @TODATE IS NOT NULL AND @TODATE <> '0' )
	BEGIN
		SET @CANCELFROMSTRING = @CANCELFROMSTRING + +' AND CCOL.Timestamp <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
	END
	--print '@CANCELSTRING = '+@CANCELSTRING
	exec (@CANCELSTRING+@CANCELFROMSTRING)

---credit notes
	set @CREDIT_NOTES_STRING = N' 
	insert into #BOOKINGTABLE (SALESORDERID,[LINEITEM ID],[LINE ITEM NO],PRODUCTID,QUANTITY,
		  [UNIT PRICE],AMOUNT,[REQUESTED DOCK DATE],[REQUESTED DATE WW NO],[REQUESTED DATE QTR NO],
		  [SCHEDULE DATE],[SCHEDULE DATE WW NO],[SCHEDULE DATE QTR NO],
		  [REVISED DATE],[REVISED DATE WW NO],[REVISED DATE QTR NO],
		  [BOOKING DATE],[BOOKING DATE WW NO],[BOOKING DATE QTR NO],
		  [CHANGE DATE],[CHANGE DATE WW NO],[CHANGE DATE QTR NO],
		  [SALES ORDER NO],CUSTOMERID,[SALES PERSON],[END CUSTOMER],
		  [REGION REP],[END USER],CUSTOMER, [PRODUCT], BOOKINGTYPE, ISCONSIGNMENT, [CREDIT_NOTE_REASON] )
	select 
		COOL.Salesorder_ID, COOL.LineItem_ID, COOL_Line_Item_No, COOL.Product_ID,CNLT.ADJUSTMENT_QTY, CNLT.DISCOUNT_PER_UNIT, CNLT.DISCOUNT_AMOUNT,  
		COOL_Requested_Date,REQUESTED_DATE_WW_NO,REQUESTED_DATE_QTR_NO, 
		COOL_Schedule_Date,SCHEDULE_DATE_WW_NO,SCHEDULE_DATE_QTR_NO,
		COOL_Revised_Date ,REVISED_DATE_WW_NO,REVISED_DATE_QTR_NO,
		so_Booking_Date,SO_BOOKING_DATE_WW_NO,SO_BOOKING_DATE_QTR_NO,
		--COOL.Timestamp,COOL.TIMESTAMP_WW_NO,COOL.TIMESTAMP_QTR_NO,
		CNT.Timestamp, CNT.CNT_APPROVED_DATE_WW_NO, CNT.CNT_APPROVED_DATE_Qtr_NO,
		SO.SalesOrder_NO,SO.Customer_ID,SO.SO_Sales_Person,
		CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) 
			THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
		END,Regional_Rep,
		CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) 
			THEN (CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) 
				THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
				ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
				END) 
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
		END,
		C_NAME,PART_NUMBER,1,SO_IS_CONSIGNMENT, CNLT.REASON '

	SET @CREDIT_NOTES_FROM_STRING = N' 
	from  
		customer_open_order_lineitem COOL,Sales_Order SO, customer c, product p, CREDIT_NOTE_TABLE CNT, CREDIT_NOTE_LINE_ITEM_TABLE CNLT
	where 
		COOL.SalesOrder_Id = SO.SalesOrder_Id 
		and COOL.product_id = p.product_id
		and so.customer_id = c.customer_id 
		AND CNT.CREDITNOTE_ID = CNLT.CN_ID
		AND COOL.LINEITEM_ID = CNLT.LINEITEM_ID
		AND CNLT.STATUS = ''APPROVED'' '

	IF @PRODUCTID <> '0'
	BEGIN
		SET @CREDIT_NOTES_FROM_STRING = @CREDIT_NOTES_FROM_STRING + ' AND COOL.Product_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PRODUCTID +'''))'
	END
	IF @CUSTOMERID <> '0'
	BEGIN
		SET @CREDIT_NOTES_FROM_STRING = @CREDIT_NOTES_FROM_STRING + ' AND SO.Customer_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @CUSTOMERID +'''))'
	END
	IF @REGION <> '0'
	BEGIN
		SET @CREDIT_NOTES_FROM_STRING = @CREDIT_NOTES_FROM_STRING + ' AND ISNULL( ISNULL( (SELECT C_Region FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID AND LEN(RTRIM(LTRIM(C_Region)) ) > 0 ),(SELECT C_Region FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID AND LEN(RTRIM(LTRIM(C_Region)) ) > 0 ) ), C.C_Region ) = '''+ @REGION+''''
	END
	IF @REPOFFICE <> '0'
	BEGIN
		SET @CREDIT_NOTES_FROM_STRING = @CREDIT_NOTES_FROM_STRING + ' AND SO.REGIONAL_REP = '''+ @REPOFFICE+''''
	END
	IF @ENDUSER <> '0'
	BEGIN
		SET @CREDIT_NOTES_FROM_STRING = @CREDIT_NOTES_FROM_STRING + ' AND CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) 
								THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
								ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
							END IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @ENDUSER +'''))'
	END
	IF @SALESPERSON <> '0'
	BEGIN
		SET @CREDIT_NOTES_FROM_STRING = @CREDIT_NOTES_FROM_STRING + ' AND C.C_SALES_PERSON IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @SALESPERSON +'''))'
	END
	IF @ENDCUSTOMER <> '0'
	BEGIN
		SET @CREDIT_NOTES_FROM_STRING = @CREDIT_NOTES_FROM_STRING + ' AND CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) 
								THEN (CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) 
									THEN ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.Customer_ID )
									ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
									END) 
								ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
							END IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @ENDCUSTOMER +'''))'
	END

	IF @ORDERTYPE IS NOT NULL AND @ORDERTYPE <> 2
	BEGIN
		SET @CREDIT_NOTES_FROM_STRING = @CREDIT_NOTES_FROM_STRING + ' AND SO_IS_CONSIGNMENT = 1 AND SO.SO_Is_Sample = '''+ CONVERT(VARCHAR,@ORDERTYPE)+''''
	END
	ELSE IF @ORDERTYPE IS NOT NULL AND @ORDERTYPE = 2
	BEGIN
		SET @CREDIT_NOTES_FROM_STRING = @CREDIT_NOTES_FROM_STRING + ' AND SO_IS_CONSIGNMENT = 0 '
	END
	
	IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' )
	BEGIN
		SET @CREDIT_NOTES_FROM_STRING = @CREDIT_NOTES_FROM_STRING +' AND CNT.Timestamp >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
	END
	IF ( @TODATE IS NOT NULL AND @TODATE <> '0' )
	BEGIN
		SET @CREDIT_NOTES_FROM_STRING = @CREDIT_NOTES_FROM_STRING + +' AND CNT.Timestamp <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
	END
	--print '@CANCELSTRING = '+@CANCELSTRING
	exec (@CREDIT_NOTES_STRING+@CREDIT_NOTES_FROM_STRING)
------

IF @REPORTTYPE = 'XLREPORT'
BEGIN
	IF @BOOKINGTYPE <> 'CHANGEDATE'
	BEGIN
		SELECT
			  [CUSTOMER],[END CUSTOMER],[END USER],[PRODUCT],[SALES ORDER NO],[LINE ITEM NO],
			  [BOOKING DATE],CONVERT(VARCHAR,YEAR([BOOKING DATE]))+'W'+CONVERT(VARCHAR,[BOOKING DATE WW NO]) AS [BOOKING DATE WW NO],CONVERT(VARCHAR,YEAR([BOOKING DATE]))+'Q'+CONVERT(VARCHAR,[BOOKING DATE QTR NO]) AS [BOOKING DATE QTR NO],
			  [CHANGE DATE], CONVERT(VARCHAR,YEAR([CHANGE DATE]))+'W'+CONVERT(VARCHAR,[CHANGE DATE WW NO]) AS [CHANGE DATE WW NO], CONVERT(VARCHAR,YEAR([CHANGE DATE]))+'Q'+CONVERT(VARCHAR,[CHANGE DATE QTR NO]) AS [CHANGE DATE QTR NO],
			  [REQUESTED DOCK DATE],CONVERT(VARCHAR,YEAR([REQUESTED DOCK DATE]))+'W'+CONVERT(VARCHAR,[REQUESTED DATE WW NO]) AS [REQUESTED DATE WW NO],CONVERT(VARCHAR,YEAR([REQUESTED DOCK DATE]))+'Q'+CONVERT(VARCHAR,[REQUESTED DATE QTR NO]) AS [REQUESTED DATE QTR NO], 
			  [SCHEDULE DATE],CONVERT(VARCHAR,YEAR([SCHEDULE DATE]))+'W'+CONVERT(VARCHAR,[SCHEDULE DATE WW NO]) AS [SCHEDULE DATE WW NO],CONVERT(VARCHAR,YEAR([SCHEDULE DATE]))+'Q'+CONVERT(VARCHAR,[SCHEDULE DATE QTR NO]) AS [SCHEDULE DATE QTR NO],
			  ISNULL([REVISED DATE],[SCHEDULE DATE]) AS [REVISED DATE], ISNULL(CONVERT(VARCHAR,YEAR([REVISED DATE]))+'W'+CONVERT(VARCHAR,[REVISED DATE WW NO]), CONVERT(VARCHAR,YEAR([SCHEDULE DATE]))+'W'+CONVERT(VARCHAR,[SCHEDULE DATE WW NO])) AS [REVISED DATE WW NO], ISNULL(CONVERT(VARCHAR,YEAR([REVISED DATE]))+'Q'+CONVERT(VARCHAR,[REVISED DATE QTR NO]), CONVERT(VARCHAR,YEAR([SCHEDULE DATE]))+'Q'+CONVERT(VARCHAR,[SCHEDULE DATE QTR NO])) AS [REVISED DATE QTR NO],
			  [UNIT PRICE], QUANTITY, AMOUNT AS [EXTENDED PRICE], [CREDIT_NOTE_REASON] AS [CREDIT/DEBIT NOTE REASON]
		FROM
			#BOOKINGTABLE 
		where [CUSTOMER] is not null
		ORDER BY DATEPART(yyyy,[BOOKING DATE]),
			  (CASE
				WHEN @BYPERIOD = 'BYWEEK' THEN [BOOKING DATE WW NO]
				WHEN @BYPERIOD = 'BYMONTH' THEN DATEPART(m,[BOOKING DATE])
				ELSE [BOOKING DATE QTR NO]
			  END)
		
	END
	ELSE
	BEGIN		
		SET @RESULT_STR = 'SELECT
			  [CUSTOMER],[END CUSTOMER],[END USER],[PRODUCT],[SALES ORDER NO],[LINE ITEM NO],
			  [BOOKING DATE],CONVERT(VARCHAR,YEAR([BOOKING DATE]))+''W''+CONVERT(VARCHAR,[BOOKING DATE WW NO]) AS [BOOKING DATE WW NO],CONVERT(VARCHAR,YEAR([BOOKING DATE]))+''Q''+CONVERT(VARCHAR,[BOOKING DATE QTR NO]) AS [BOOKING DATE QTR NO],
			  [CHANGE DATE], CONVERT(VARCHAR,YEAR([CHANGE DATE]))+''W''+CONVERT(VARCHAR,[CHANGE DATE WW NO]) AS [CHANGE DATE WW NO], CONVERT(VARCHAR,YEAR([CHANGE DATE]))+''Q''+CONVERT(VARCHAR,[CHANGE DATE QTR NO]) AS [CHANGE DATE QTR NO],
			  [REQUESTED DOCK DATE],CONVERT(VARCHAR,YEAR([REQUESTED DOCK DATE]))+''W''+CONVERT(VARCHAR,[REQUESTED DATE WW NO]) AS [REQUESTED DATE WW NO],CONVERT(VARCHAR,YEAR([REQUESTED DOCK DATE]))+''Q''+CONVERT(VARCHAR,[REQUESTED DATE QTR NO]) AS [REQUESTED DATE QTR NO], 
			  [SCHEDULE DATE],CONVERT(VARCHAR,YEAR([SCHEDULE DATE]))+''W''+CONVERT(VARCHAR,[SCHEDULE DATE WW NO]) AS [SCHEDULE DATE WW NO],CONVERT(VARCHAR,YEAR([SCHEDULE DATE]))+''Q''+CONVERT(VARCHAR,[SCHEDULE DATE QTR NO]) AS [SCHEDULE DATE QTR NO],
			  ISNULL([REVISED DATE],[SCHEDULE DATE]) AS [REVISED DATE], ISNULL(CONVERT(VARCHAR,YEAR([REVISED DATE]))+''W''+CONVERT(VARCHAR,[REVISED DATE WW NO]), CONVERT(VARCHAR,YEAR([SCHEDULE DATE]))+''W''+CONVERT(VARCHAR,[SCHEDULE DATE WW NO])) AS [REVISED DATE WW NO], ISNULL(CONVERT(VARCHAR,YEAR([REVISED DATE]))+''Q''+CONVERT(VARCHAR,[REVISED DATE QTR NO]), CONVERT(VARCHAR,YEAR([SCHEDULE DATE]))+''Q''+CONVERT(VARCHAR,[SCHEDULE DATE QTR NO])) AS [REVISED DATE QTR NO],
			  [UNIT PRICE], QUANTITY, AMOUNT AS [EXTENDED PRICE], [CREDIT_NOTE_REASON] AS [CREDIT/DEBIT NOTE REASON]
		FROM
			#BOOKINGTABLE 
		where [CUSTOMER] is not null '
		
		IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' )
		BEGIN
			SET @RESULT_STR = @RESULT_STR +' AND [CHANGE DATE] >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
		END
		IF ( @TODATE IS NOT NULL AND @TODATE <> '0' )
		BEGIN
			SET @RESULT_STR = @RESULT_STR + +' AND [CHANGE DATE] <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
		END
		SET @RESULT_STR = @RESULT_STR + ' ORDER BY DATEPART(yyyy,[BOOKING DATE]),
			  (CASE
				WHEN ''' +  @BYPERIOD + '''= ''BYWEEK'' THEN isnull([CHANGE DATE WW NO],[BOOKING DATE WW NO])
				WHEN ''' + @BYPERIOD + '''= ''BYMONTH'' THEN isnull( DATEPART(m,[CHANGE DATE]),DATEPART(m,[BOOKING DATE]) )
				ELSE isnull( [CHANGE DATE QTR NO],[BOOKING DATE QTR NO])
			  END) '

		EXEC (@RESULT_STR)
		
	END
END
ELSE
BEGIN
	IF @BOOKINGTYPE <> 'CHANGEDATE'
	BEGIN
		SELECT 
			  SALESORDERID,[LINEITEM ID],[CHANGE NO],[LINE ITEM NO],PRODUCTID,QUANTITY,
			  [UNIT PRICE],[REQUESTED DOCK DATE],[REQUESTED DATE WW NO],[REQUESTED DATE QTR NO],
			  [SCHEDULE DATE],[SCHEDULE DATE WW NO],[SCHEDULE DATE QTR NO],
			  [REVISED DATE],[REVISED DATE WW NO],[REVISED DATE QTR NO],
                          [BOOKING DATE],[BOOKING DATE WW NO],[BOOKING DATE QTR NO],
			  [CHANGE DATE],[CHANGE DATE WW NO],[CHANGE DATE QTR NO],
			  [SALES ORDER NO],CUSTOMERID,[SALES PERSON],[END CUSTOMER],
			  [REGION REP],[END USER],CUSTOMER, [PRODUCT], BOOKINGTYPE,
			  DATEPART(yyyy,[BOOKING DATE]) as act_year,
			  (CASE
				WHEN @BYPERIOD = 'BYWEEK' THEN [BOOKING DATE WW NO]
				WHEN @BYPERIOD = 'BYMONTH' THEN DATEPART(m,[BOOKING DATE])
				ELSE [BOOKING DATE QTR NO]
			  END) as periodic_value,AMOUNT AS [EXTENDED PRICE],ISCONSIGNMENT
		FROM 
			#BOOKINGTABLE 
		where [CUSTOMER] is not null
		order by DATEPART(yyyy,[BOOKING DATE]),
			  (CASE
				WHEN @BYPERIOD = 'BYWEEK' THEN [BOOKING DATE WW NO]
				WHEN @BYPERIOD = 'BYMONTH' THEN DATEPART(m,[BOOKING DATE])
				ELSE [BOOKING DATE QTR NO]
			  END),[BOOKING DATE] 
		
	END
	ELSE
	BEGIN
		SET @RESULT_STR = 'SELECT
			 SALESORDERID,[LINEITEM ID],[CHANGE NO],[LINE ITEM NO],PRODUCTID,QUANTITY,
			  [UNIT PRICE],[REQUESTED DOCK DATE],[REQUESTED DATE WW NO],[REQUESTED DATE QTR NO],
			  [SCHEDULE DATE],[SCHEDULE DATE WW NO],[SCHEDULE DATE QTR NO],
			  [REVISED DATE],[REVISED DATE WW NO],[REVISED DATE QTR NO],
			  [BOOKING DATE],[BOOKING DATE WW NO],[BOOKING DATE QTR NO],
			  [CHANGE DATE],[CHANGE DATE WW NO],[CHANGE DATE QTR NO],
			  [SALES ORDER NO],CUSTOMERID,[SALES PERSON],[END CUSTOMER],
			  [REGION REP],[END USER],CUSTOMER, [PRODUCT], BOOKINGTYPE,
			  DATEPART(yyyy,[CHANGE DATE]) as act_year,
			  (CASE
				WHEN ''' + @BYPERIOD + ''' = ''BYWEEK'' THEN isnull([CHANGE DATE WW NO],[BOOKING DATE WW NO])
				WHEN ''' + @BYPERIOD + ''' = ''BYMONTH'' THEN isnull( DATEPART(m,[CHANGE DATE]),DATEPART(m,[BOOKING DATE]) )
				ELSE isnull( [CHANGE DATE QTR NO],[BOOKING DATE QTR NO])
			  END) as periodic_value,AMOUNT AS [EXTENDED PRICE],ISCONSIGNMENT
		FROM
			#BOOKINGTABLE 
		where [CUSTOMER] is not null '
		IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' )
		BEGIN
			SET @RESULT_STR = @RESULT_STR +' AND [CHANGE DATE] >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
		END
		IF ( @TODATE IS NOT NULL AND @TODATE <> '0' )
		BEGIN
			SET @RESULT_STR = @RESULT_STR + +' AND [CHANGE DATE] <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
		END
		SET @RESULT_STR = @RESULT_STR + ' order by DATEPART(yyyy,[CHANGE DATE]),
			  (CASE
				WHEN ''' + @BYPERIOD + '''= ''BYWEEK'' THEN isnull([CHANGE DATE WW NO],[BOOKING DATE WW NO])
				WHEN ''' + @BYPERIOD + '''= ''BYMONTH'' THEN isnull( DATEPART(m,[CHANGE DATE]),DATEPART(m,[BOOKING DATE]) )
				ELSE isnull( [CHANGE DATE QTR NO],[BOOKING DATE QTR NO])
			  END) ,[CHANGE DATE]' 
		EXEC (@RESULT_STR)
	END
END

END


